package com.lovo.dao.impl;

import com.lovo.bean.CutPageBean;
import com.lovo.bean.DeptBean;
import com.lovo.bean.EmpBean;
import com.lovo.dao.BaseDao;
import com.lovo.dao.IEmpDao;

import java.sql.SQLException;
import java.time.LocalDate;

public class EmpDaoImpl extends BaseDao implements IEmpDao {
    @Override
    public CutPageBean<EmpBean> findByItem(int pageNo, String name, String education) {
        CutPageBean<EmpBean> cutPageBean = new CutPageBean<>();
        String sql = "select * from emp e left join dept d on e.emp_dept_id=d.dept_id";
        String dynamicSql = "";
        if(name != null && name.length() > 0){
            dynamicSql += " where emp_name like '%"+name+"%'";
        }else if(education != null && education.length() > 0){
            dynamicSql += " where education like '%"+education+"%'";
        }
        sql += dynamicSql + " limit "+(pageNo - 1) * CutPageBean.PAGESIZE+","+CutPageBean.PAGESIZE;
        try{
            setConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){

                cutPageBean.getList().add(fillEmp());
            }
            ps = con.prepareStatement("select count(*) cnt from emp e left join dept d on e.emp_dept_id=d.dept_id" + dynamicSql);
            rs = ps.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConnection();
        }

        return cutPageBean;
    }

    @Override
    public void add(EmpBean empBean) {
        try{
            this.setConnection();
            ps = con.prepareStatement("insert into emp(emp_name,gender,education,born,phone,emp_dept_id,face) values(?,?,?,?,?,?,?)");
            ps.setObject(1,empBean.getName());
            ps.setObject(2,empBean.getGender());
            ps.setObject(3,empBean.getEducation());
            ps.setObject(4,empBean.getBorn());
            ps.setObject(5,empBean.getPhone());
            ps.setObject(6,empBean.getDeptObj().getId());
            ps.setObject(7,empBean.getFace());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void delete(int id) {
        try{
            this.setConnection();
            ps = con.prepareStatement("delete from emp where emp_id=?");
            ps.setInt(1,id);
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void update(EmpBean empBean) {
        try {
            this.setConnection();
            ps = con.prepareStatement("update emp set emp_name=?,gender=?,education=?,born=?,emp_dept_id=?,phone=?,face=? where emp_id=?");
            ps.setObject(1, empBean.getName());
            ps.setObject(2, empBean.getGender());
            ps.setObject(3, empBean.getEducation());
            ps.setObject(4, empBean.getBorn());
            ps.setObject(5, empBean.getDeptObj().getId());
            ps.setObject(6, empBean.getPhone());
            ps.setObject(7, empBean.getFace());
            ps.setObject(8, empBean.getId());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }
    private EmpBean fillEmp() throws SQLException {
        EmpBean empBean = new EmpBean();
        empBean.setId(rs.getInt("emp_id"));
        empBean.setName(rs.getString("emp_name"));
        empBean.setGender(rs.getString("gender"));
        empBean.setEducation(rs.getString("education"));
        empBean.setPhone(rs.getString("phone"));
        empBean.setFace(rs.getString("face"));
        if(rs.getString("born") != null){
            empBean.setBorn(LocalDate.parse(rs.getString("born")));
        }
        DeptBean deptBean = new DeptBean();
        deptBean.setId(rs.getInt("emp_dept_id"));
        deptBean.setName(rs.getString("dept_name"));
        empBean.setDeptObj(deptBean);
        return empBean;
    }
    public static void main(String[] args) {
        IEmpDao empDao = new EmpDaoImpl();
        System.out.println(empDao.findByItem(1,null,null));
    }
}
